from sqlalchemy import create_engine, Column, Integer, String, DateTime, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy import func
from sqllite_pre import Base,get_db
from fastapi import FastAPI, UploadFile, File, HTTPException, Depends, status
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import JSONResponse
from fastapi.security import OAuth2PasswordBearer, OAuth2PasswordRequestForm
from typing import List, Optional, Union
from pydantic import BaseModel, EmailStr
from users import User


#搜索记录表
class SearchHistory(Base):
    __tablename__ = "search_history"  # 根据实际表名调整

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, nullable=False)
    search_content = Column(String(200), nullable=False)
    category = Column(String(20), nullable=False)

def add_search_record(
    db: Session,
    user_id: int,
    search_content: str,
    category: str
) -> None:
    """添加搜索记录"""
    new_record = SearchHistory(
        user_id=user_id,
        search_content=search_content,
        category=category
    )
    db.add(new_record)
    db.commit()


def get_search_history(db: Session,user_id: int) -> List[SearchHistory]:
    """根据用户ID查询搜索历史记录
    Args:
        user_id (int): 用户ID，传入-1表示查询所有记录.
        db (Session): 数据库会话
    Returns:
        List[SearchHistory]: 符合条件的搜索记录列表
    """
    if user_id == -1:
        return db.query(SearchHistory).all()
    else:
        return db.query(SearchHistory).filter_by(user_id=user_id).order_by(SearchHistory.id.desc()).limit(5).all()


async def get_user_search_contents(db: Session, user_id: int, num: int = 5) -> List[str]:
    """根据用户ID查询指定数量的最新搜索内容列表（按自增主键倒序排序）
    Args:
        user_id (int): 目标用户ID
        num (int): 需要返回的最新数据条数（正整数，默认10条，若为0或负数则返回所有记录）
        db (Session): 数据库会话
    Returns:
        List[str]: 用户的搜索内容列表（按插入时间倒序，取最新num条）
    """
    try:
        # 校验num参数合法性
        if num <= 0:
            # 返回所有记录
            search_records = db.query(SearchHistory).filter(
                SearchHistory.user_id == user_id
            ).all()
        else:
            # 按自增主键倒序排序（假设id越大表示记录越新），取前num条
            search_records = db.query(SearchHistory).filter(
                SearchHistory.user_id == user_id
            ).order_by(SearchHistory.id.desc()).limit(num).all()

        return [record.search_content for record in search_records]
    except Exception as e:
        print(f"查询用户{user_id}搜索记录失败:", e)
        return []


# 删除搜索记录的函数
def delete_search_history(db: Session, record_id: int, current_user: User):
    # 查询指定ID的搜索记录
    record = db.query(SearchHistory).filter(SearchHistory.id == record_id).first()
    if not record:
        raise HTTPException(status_code=404, detail="搜索记录不存在")

    # 检查权限：仅允许记录的所有者或管理员删除
    if record.user_id != current_user.id and not current_user.is_admin:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="无权删除该搜索记录"
        )

    # 执行删除
    db.delete(record)
    db.commit()
    return {"message": "搜索记录删除成功"}

#搜索记录模式

#分类搜索数
class CategoryCount(BaseModel):
    category: str
    count: int

class SearchCategoryResponse(BaseModel):
    data: List[CategoryCount]

def get_user_category_counts(db: Session, user_id: int) -> List[CategoryCount]:
    """返回指定用户在各个搜索类别中的记录数量"""
    # 查询语句
    category_counts = db.query(
        SearchHistory.category,          # 分组字段
        func.count(SearchHistory.category).label('count')  # 统计字段
    ).filter(SearchHistory.user_id == user_id).group_by(SearchHistory.category).all()  # 执行查询

    # 将查询结果转换为列表
    return [
        CategoryCount(category=category, count=count)
        for category, count in category_counts
    ]

def get_other_search_random(db: Session, user_id: int) -> List[SearchHistory]:
    records = (
            db.query(SearchHistory)
            .filter(SearchHistory.user_id != user_id)
            .order_by(func.random())
            .limit(5)
            .all()
        )
    return records